package com.apes.fico.finance.respository;


import com.apes.fico.finance.model.costcollect.StaffSalaries;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface StaffSalariesRepository extends CustomRepository<StaffSalaries, String> {

        @Query(value = "SELECT T.DEPTID," +
                "       SUM(T.XSTC) XSTC," +
                "       SUM(T.SGTC) SGTC," +
                "       SUM(T.BTJE) BTJE," +
                "       T.PERSON" +
                "  FROM (SELECT A.DEPT_ID AS DEPTID," +
                "               (SUM(A.TC_TOTAL) - SUM(A.SGTC)) AS XSTC," +
                "               0 AS SGTC," +
                "               0 AS BTJE," +
                "               A.PERSON_ID AS PERSON" +
                "          FROM TC_RYTCRBB A" +
                "         WHERE EXTRACT(MONTH FROM A.TJRQ) = :months" +
                "           AND EXTRACT(YEAR FROM A.TJRQ) = :years" +
                "           AND A.TC_TOTAL <> 0" +
                "         GROUP BY A.PERSON_ID, A.DEPT_ID" +
                "        UNION ALL" +
                "        SELECT A.DEPT_ID AS DEPTID," +
                "               0 AS XSTC," +
                "               SUM(A.SGTC) AS SGTC," +
                "               0 AS BTJE," +
                "               A.PERSON_ID AS PERSON" +
                "          FROM TC_RYTCRBB A" +
                "         WHERE EXTRACT(MONTH FROM A.TJRQ) = :months" +
                "           AND EXTRACT(YEAR FROM A.TJRQ) = :years" +
                "           AND A.SGTC <> 0" +
                "         GROUP BY A.PERSON_ID, A.DEPT_ID" +
                "        UNION ALL" +
                "        SELECT A.BRANCH_ID AS DEPTID," +
                "               0 AS XSTC," +
                "               0 AS SGTC," +
                "               SUM(A.SUBSIDY_AMT) AS BTJE," +
                "               (SELECT B.ID" +
                "                  FROM HR_EMPLOYEE T, MAS_PARTY_ROLE B, MAS_PARTY C" +
                "                 WHERE B.PARTY_ID = C.ID" +
                "                   AND B.ROLE_ID = 'Employee'" +
                "                   AND C.NAME = T.NAME" +
                "                   AND T.ID = A.SUPPORT_UID) AS PERSON" +
                "          FROM HR_SUPPORT_APPLICATION_AFFIRM A" +
                "         WHERE EXTRACT(MONTH FROM A.FINISH_DATE) = :months" +
                "           AND EXTRACT(YEAR FROM A.FINISH_DATE) = :years" +
                "           AND A.SUBSIDY_AMT <> 0" +
                "         GROUP BY A.SUPPORT_UID, A.BRANCH_ID) T" +
                " GROUP BY T.PERSON, T.DEPTID",nativeQuery = true)
        List<Map<String,Object>> TCJE(@Param("months") String months, @Param("years") String years);

        @Query(value = "SELECT * FROM GL_STAFF_SALARIES A WHERE EXTRACT(MONTH FROM A.YEAR_MONTH) = :months AND EXTRACT(YEAR FROM A.YEAR_MONTH) = :years AND A.DEPT_ID = :deptId AND A.PERSON_ID = :personId",nativeQuery = true)
        StaffSalaries findByYearMonthAndDeptAndPerson(@Param("months") String months, @Param("years") String years, @Param("deptId") String deptId,@Param("personId") String personId);

        @Query(value = "SELECT a.id,(SELECT post_group_id FROM HR_WORK_POST WHERE ID = A.POST_ID) post_group_id FROM hr_Employee A WHERE A.name = :name",nativeQuery = true)
        Map<String,Object> findByName(@Param("name") String name);

        @Query(value = "SELECT a.* FROM gl_staff_salaries a where a.dept_id = :dept and a.company_id = :company\n" +
                "   and  A.YEAR_MONTH = to_date(:months,'yyyy-mm') and a.person_id = :person and a.type = :type and a.valid = 1",nativeQuery = true)
        StaffSalaries findByDeptCompanyPerson(@Param("dept") String dept, @Param("company") String company, @Param("person") String person, @Param("months") String months, @Param("type") String type);


}
